﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class BidderDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public BidderDAL()
        {

        }

        public BidderInfo GetBidder(int BidderSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            BidderInfo info = new BidderInfo();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@icm_bidder_seq", BidderSeq);

                strSQL = "SELECT b.*"
                            + ",concat(mc.ICM_CON_NAME_TH, ' ',case when Length(mc.ICM_CON_NAME_ENG) > 0 then concat('(',mc.ICM_CON_NAME_ENG,')') else ' ' end) as ICM_CON_NAME"
                            + ", v.ICM_VAT_NAME" 
                            + ", dt.ICM_DUR_TYPE_NAME"
                        + " FROM icm_bidder b"
                            + " LEFT JOIN ICM_MAS_CONTACT mc ON b.ICM_CON_SEQ = mc.ICM_CON_SEQ"
                            + " LEFT JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ"
                            + " LEFT JOIN ICM_SYS_DURATION_TYPE dt ON b.ICM_DUR_TYPE_SEQ = dt.ICM_DUR_TYPE_SEQ"
                        + " WHERE ICM_BIDDER_SEQ = @icm_bidder_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    PurchaseRequestInfo infoPr = new PurchaseRequestInfo();
                    MasterContactInfo infoMasContact = new MasterContactInfo();
                    SysVatInfo infoVat = new SysVatInfo();
                    SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                    BidInfo infoBid = new BidInfo();

                    info.Seq = Convert.ToInt32(dr[0]["ICM_BIDDER_SEQ"]);
                    infoMasContact.Name_th = dr[0]["ICM_CON_NAME"].ToString();
                    infoMasContact.Seq = (Convert.IsDBNull(dr[0]["ICM_CON_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_CON_SEQ"]));
                    infoPr.Seq = (Convert.IsDBNull(dr[0]["ICM_PR_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_PR_SEQ"]));
                    infoBid.Seq = (Convert.IsDBNull(dr[0]["ICM_BID_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_BID_SEQ"]));
                    infoVat.Name = dr[0]["ICM_VAT_NAME"].ToString();
                    infoVat.Seq = (Convert.IsDBNull(dr[0]["ICM_VAT_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_VAT_SEQ"]));
                    info.VatPercent = (Convert.IsDBNull(dr[0]["VAT_PERCENT"]) ? null : (Nullable<double>)(dr[0]["VAT_PERCENT"]));
                    info.Date = (Convert.IsDBNull(dr[0]["ICM_BIDDER_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["ICM_BIDDER_DATE"]));
                    info.DueDay = (Convert.IsDBNull(dr[0]["DUE_DAY"]) ? null : (Nullable<int>)(dr[0]["DUE_DAY"]));
                    info.DueDate = (Convert.IsDBNull(dr[0]["DUE_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["DUE_DATE"]));
                    infoDurationType.Name = dr[0]["ICM_DUR_TYPE_NAME"].ToString();
                    infoDurationType.Seq = (Convert.IsDBNull(dr[0]["ICM_DUR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_DUR_TYPE_SEQ"]));
                    info.ReceiveStatus = dr[0]["RECEIVE_STATUS"].ToString();
                    info.OfferStatus = dr[0]["OFFER_STATUS"].ToString();
                    info.WinnerStatus = dr[0]["WINNER_STATUS"].ToString();
                    info.ReceiveDate = (Convert.IsDBNull(dr[0]["ICM_RECEIVE_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["ICM_RECEIVE_DATE"]));
                    info.ReceiveReceiptNo = dr[0]["RECEIVE_RECEIPT_NO"].ToString();
                    info.OfferDate = (Convert.IsDBNull(dr[0]["ICM_OFFER_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["ICM_OFFER_DATE"]));
                    info.OfferTime = (Convert.IsDBNull(dr[0]["ICM_OFFER_TIME"]) ? null : (Nullable<TimeSpan>)(dr[0]["ICM_OFFER_TIME"]));

                    info.InfoPurchaseRequest = infoPr;
                    info.InfoBid = infoBid;
                    info.InfoMasContact = infoMasContact;
                    info.InfoVat = infoVat;
                    info.InfoDurationType = infoDurationType;

                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public BidderInfo GetBidderContact(int bidder_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            BidderInfo info = new BidderInfo();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@icm_bidder_seq", bidder_seq);

                strSQL = "SELECT bd.ICM_PR_SEQ"
                            + ", bd.ICM_BID_SEQ"
                            + ", bd.ICM_DUR_TYPE_SEQ"
                            + ", bd.DUE_DATE"
                            + ", bd.DUE_DAY"
                            + ", c.*"
                            + ", cg.ICM_CON_GRP_NAME"
                            + ", b.YEAR_TYPE_SEQ AS BID_YEAR_TYPE_SEQ"
                            + ", b.BGM_YEAR AS BID_BGM_YEAR"
                            + ", pr.YEAR_TYPE_SEQ AS PR_YEAR_TYPE_SEQ"
                            + ", pr.BGM_YEAR AS PR_BGM_YEAR"
                        + " FROM ICM_BIDDER bd"
                            + " LEFT JOIN ICM_PURCHASE_REQUEST pr ON bd.ICM_PR_SEQ = pr.ICM_PR_SEQ"
                            + " LEFT JOIN ICM_BID b ON bd.ICM_BID_SEQ = b.ICM_BID_SEQ"
                            + " LEFT JOIN ICM_MAS_CONTACT c ON bd.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_SYS_CONTACT_GROUP cg ON c.ICM_CON_GRP_SEQ = cg.ICM_CON_GRP_SEQ "
                        + " WHERE bd.ICM_BIDDER_SEQ = @icm_bidder_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    MasterContactInfo infoMasContact = new MasterContactInfo();
                    SysContactGroupInfo infoGroup = new SysContactGroupInfo();
                    SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                    PurchaseRequestInfo infoPr = new PurchaseRequestInfo();
                    BidInfo infoBid = new BidInfo();
                    SysYearTypeInfo infoPrYearType = new SysYearTypeInfo();
                    SysYearTypeInfo infoBidYearType = new SysYearTypeInfo();

                    infoPrYearType.Seq = (Convert.IsDBNull(dr[0]["PR_YEAR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["PR_YEAR_TYPE_SEQ"]));
                    infoPr.InfoYearType = infoPrYearType;
                    infoPr.BgmYear = (Convert.IsDBNull(dr[0]["PR_BGM_YEAR"]) ? null : (Nullable<int>)(dr[0]["PR_BGM_YEAR"]));
                    infoPr.Seq = (Convert.IsDBNull(dr[0]["ICM_PR_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_PR_SEQ"]));
                    infoBidYearType.Seq = (Convert.IsDBNull(dr[0]["BID_YEAR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["BID_YEAR_TYPE_SEQ"]));
                    infoBid.InfoYearType = infoBidYearType;
                    infoBid.Year = (Convert.IsDBNull(dr[0]["BID_BGM_YEAR"]) ? null : (Nullable<int>)(dr[0]["BID_BGM_YEAR"]));
                    infoBid.Seq = (Convert.IsDBNull(dr[0]["ICM_BID_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_BID_SEQ"]));
                    infoDurationType.Seq = (Convert.IsDBNull(dr[0]["ICM_DUR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_DUR_TYPE_SEQ"]));
                    infoGroup.Seq = (Convert.IsDBNull(dr[0]["ICM_CON_GRP_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_CON_GRP_SEQ"]));
                    infoGroup.Name = (dr[0]["ICM_CON_GRP_NAME"]).ToString();
                    infoMasContact.Seq = Convert.ToInt32(dr[0]["ICM_CON_SEQ"]);
                    infoMasContact.Gfmis_code = (dr[0]["ICM_CON_GFMIS_CODE"]).ToString();
                    infoMasContact.Name_th = (dr[0]["ICM_CON_NAME_TH"]).ToString();
                    infoMasContact.Name_eng = (dr[0]["ICM_CON_NAME_ENG"]).ToString();
                    infoMasContact.All_address += ((dr[0]["ICM_CON_COUNTRY"]).ToString().Length > 0 ? " ประเทศ" + (dr[0]["ICM_CON_COUNTRY"]).ToString() : "");
                    infoMasContact.All_address += ((dr[0]["ICM_CON_BUILDING"]).ToString().Length > 0 ? " อาคาร" + (dr[0]["ICM_CON_BUILDING"]).ToString() : "");
                    infoMasContact.All_address += ((dr[0]["ICM_CON_FLOOR"]).ToString().Length > 0 ? " ชั้น " + (dr[0]["ICM_CON_FLOOR"]).ToString() : "");
                    infoMasContact.All_address += ((dr[0]["ICM_CON_VILLAGE"]).ToString().Length > 0 ? " หมู่บ้าน" + (dr[0]["ICM_CON_VILLAGE"]).ToString() : "");
                    infoMasContact.All_address += ((dr[0]["ICM_CON_ADDRESS"]).ToString().Length > 0 ? " บ้านเลขที่ " + (dr[0]["ICM_CON_ADDRESS"]).ToString() : "");
                    infoMasContact.All_address += ((dr[0]["ICM_CON_ROAD"]).ToString().Length > 0 ? " ถนน" + (dr[0]["ICM_CON_ROAD"]).ToString() : "");
                    infoMasContact.All_address += ((dr[0]["ICM_CON_LANE"]).ToString().Length > 0 ? " ซอย " + (dr[0]["ICM_CON_LANE"]).ToString() : "");
                    infoMasContact.All_address += ((dr[0]["ICM_CON_DISTRICT"]).ToString().Length > 0 ? " แขวง/ตำบล" + (dr[0]["ICM_CON_DISTRICT"]).ToString() : "");
                    infoMasContact.All_address += ((dr[0]["ICM_CON_AMPHUR"]).ToString().Length > 0 ? " เขต/อำเภอ" + (dr[0]["ICM_CON_AMPHUR"]).ToString() : "");
                    infoMasContact.All_address += ((dr[0]["ICM_CON_POST"]).ToString().Length > 0 ? " รหัสไปรษณีย์ " + (dr[0]["ICM_CON_POST"]).ToString() : "");
                    infoMasContact.All_address += ((dr[0]["ICM_CON_PROVINCE"]).ToString().Length > 0 ? " จังหวัด" + (dr[0]["ICM_CON_PROVINCE"]).ToString() : "");
                    infoMasContact.Tel = (dr[0]["ICM_CON_TEL"]).ToString();
                    infoMasContact.Email = (dr[0]["ICM_CON_EMAIL"]).ToString();
                    info.DueDay = (Convert.IsDBNull(dr[0]["DUE_DAY"]) ? null : (Nullable<int>)(dr[0]["DUE_DAY"]));
                    info.DueDate = (Convert.IsDBNull(dr[0]["DUE_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["DUE_DATE"]));

                    infoMasContact.InfoGroup = infoGroup;
                    info.InfoMasContact = infoMasContact;
                    info.InfoPurchaseRequest = infoPr;
                    info.InfoBid = infoBid;
                    info.InfoDurationType = infoDurationType;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public IList<BidderInfo> GetSeller(int PrSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<BidderInfo> infoList = new List<BidderInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@icm_pr_seq", PrSeq);

                strSQL = "SELECT b.ICM_BIDDER_SEQ"
                            + ", b.VAT_PERCENT"
                            + ", cg.ICM_CON_GRP_NAME"
                            + ", c.*"
                            + ", CONCAT(c.ICM_CON_NAME_TH, ' ',CASE WHEN LENGTH(c.ICM_CON_NAME_ENG) > 0 THEN CONCAT('(',c.ICM_CON_NAME_ENG,')') ELSE ' ' END) AS ICM_CON_NAME"
                            + ", v.ICM_VAT_SEQ AS BIDDER_VAT_SEQ"
                            + ", (select sum(coalesce(net_amount,0)) from icm_bidder_quotation where icm_bidder_seq = b.icm_bidder_seq) as total_amount"
                            + ", v.ICM_VAT_NAME" 
                        + " FROM ICM_BIDDER b"
                            + " INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ "
                            + " INNER JOIN ICM_SYS_CONTACT_GROUP cg ON c.ICM_CON_GRP_SEQ = cg.ICM_CON_GRP_SEQ "
                            + " INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                        + " WHERE b.ICM_PR_SEQ = @icm_pr_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BidderInfo info = new BidderInfo();
                        MasterContactInfo infoMasContact = new MasterContactInfo();
                        SysContactGroupInfo infoGroup = new SysContactGroupInfo();
                        SysVatInfo infoVat = new SysVatInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_BIDDER_SEQ"]);
                        info.VatPercent = (Convert.IsDBNull(dr[i]["VAT_PERCENT"])) ? null : (Nullable<double>)Convert.ToInt32(dr[i]["VAT_PERCENT"]);
                        infoGroup.Seq = (Convert.IsDBNull(dr[i]["ICM_CON_GRP_SEQ"])) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ICM_CON_GRP_SEQ"]);
                        infoGroup.Name = dr[i]["ICM_CON_GRP_NAME"].ToString();
                        infoVat.Seq = (Convert.IsDBNull(dr[i]["BIDDER_VAT_SEQ"])) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["BIDDER_VAT_SEQ"]);
                        infoVat.Name = dr[i]["ICM_VAT_NAME"].ToString();
                        infoMasContact.Gfmis_code = dr[i]["ICM_CON_GFMIS_CODE"].ToString();
                        infoMasContact.Name_th = dr[i]["ICM_CON_NAME"].ToString();
                        info.TotalAmount = Convert.ToDouble(dr[i]["TOTAL_AMOUNT"]);
                        infoMasContact.All_address += ((dr[i]["ICM_CON_COUNTRY"]).ToString().Length > 0 ? " ประเทศ" + (dr[i]["ICM_CON_COUNTRY"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_BUILDING"]).ToString().Length > 0 ? " อาคาร" + (dr[i]["ICM_CON_BUILDING"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_FLOOR"]).ToString().Length > 0 ? " ชั้น " + (dr[i]["ICM_CON_FLOOR"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_VILLAGE"]).ToString().Length > 0 ? " หมู่บ้าน" + (dr[i]["ICM_CON_VILLAGE"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_ADDRESS"]).ToString().Length > 0 ? " บ้านเลขที่ " + (dr[i]["ICM_CON_ADDRESS"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_ROAD"]).ToString().Length > 0 ? " ถนน" + (dr[i]["ICM_CON_ROAD"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_LANE"]).ToString().Length > 0 ? " ซอย " + (dr[i]["ICM_CON_LANE"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_DISTRICT"]).ToString().Length > 0 ? " แขวง/ตำบล" + (dr[i]["ICM_CON_DISTRICT"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_AMPHUR"]).ToString().Length > 0 ? " เขต/อำเภอ" + (dr[i]["ICM_CON_AMPHUR"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_POST"]).ToString().Length > 0 ? " รหัสไปรษณีย์ " + (dr[i]["ICM_CON_POST"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_PROVINCE"]).ToString().Length > 0 ? " จังหวัด" + (dr[i]["ICM_CON_PROVINCE"]).ToString() : "");

                        info.InfoGroup = infoGroup;
                        info.InfoMasContact = infoMasContact;
                        info.InfoVat = infoVat;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<BidderInfo> GetBidderFromBid(int BidSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<BidderInfo> infoList = new List<BidderInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@icm_bid_seq", BidSeq);

                strSQL = "SELECT b.*"
                        + ", cg.ICM_CON_GRP_NAME"
                        + ", c.*"
                        + ", CONCAT(c.ICM_CON_NAME_TH, ' ',CASE WHEN LENGTH(c.ICM_CON_NAME_ENG) > 0 THEN CONCAT('(',c.ICM_CON_NAME_ENG,')') ELSE ' ' END) AS ICM_CON_NAME "
                        + ", v.ICM_VAT_SEQ AS BIDDER_VAT_SEQ"
                        + ", v.ICM_VAT_NAME"
                    + " FROM ICM_BIDDER b"
                        + " INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ "
                        + " INNER JOIN ICM_SYS_CONTACT_GROUP cg ON c.ICM_CON_GRP_SEQ = cg.ICM_CON_GRP_SEQ "
                        + " INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                    + " WHERE b.ICM_BID_SEQ = @icm_bid_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BidderInfo info = new BidderInfo();
                        MasterContactInfo infoMasContact = new MasterContactInfo();
                        SysContactGroupInfo infoGroup = new SysContactGroupInfo();
                        SysVatInfo infoVat = new SysVatInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_BIDDER_SEQ"]);
                        info.VatPercent = (Convert.IsDBNull(dr[i]["VAT_PERCENT"])) ? null : (Nullable<double>)Convert.ToInt32(dr[i]["VAT_PERCENT"]);
                        info.ReceiveReceiptNo = dr[i]["RECEIVE_RECEIPT_NO"].ToString();
                        info.ReceiveStatus = dr[i]["RECEIVE_STATUS"].ToString();
                        info.OfferStatus = dr[i]["OFFER_STATUS"].ToString();
                        info.WinnerStatus = dr[i]["WINNER_STATUS"].ToString();
                        infoVat.Seq = (Convert.IsDBNull(dr[i]["BIDDER_VAT_SEQ"])) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["BIDDER_VAT_SEQ"]);
                        infoVat.Name = dr[i]["ICM_VAT_NAME"].ToString();
                        infoGroup.Seq = (Convert.IsDBNull(dr[i]["ICM_CON_GRP_SEQ"])) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ICM_CON_GRP_SEQ"]);
                        infoGroup.Name = dr[i]["ICM_CON_GRP_NAME"].ToString();
                        infoMasContact.Gfmis_code = dr[i]["ICM_CON_GFMIS_CODE"].ToString();
                        infoMasContact.Name_th = dr[i]["ICM_CON_NAME"].ToString();
                        infoMasContact.All_address += ((dr[i]["ICM_CON_COUNTRY"]).ToString().Length > 0 ? " ประเทศ" + (dr[i]["ICM_CON_COUNTRY"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_BUILDING"]).ToString().Length > 0 ? " อาคาร" + (dr[i]["ICM_CON_BUILDING"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_FLOOR"]).ToString().Length > 0 ? " ชั้น " + (dr[i]["ICM_CON_FLOOR"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_VILLAGE"]).ToString().Length > 0 ? " หมู่บ้าน" + (dr[i]["ICM_CON_VILLAGE"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_ADDRESS"]).ToString().Length > 0 ? " บ้านเลขที่ " + (dr[i]["ICM_CON_ADDRESS"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_ROAD"]).ToString().Length > 0 ? " ถนน" + (dr[i]["ICM_CON_ROAD"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_LANE"]).ToString().Length > 0 ? " ซอย " + (dr[i]["ICM_CON_LANE"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_DISTRICT"]).ToString().Length > 0 ? " แขวง/ตำบล" + (dr[i]["ICM_CON_DISTRICT"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_AMPHUR"]).ToString().Length > 0 ? " เขต/อำเภอ" + (dr[i]["ICM_CON_AMPHUR"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_POST"]).ToString().Length > 0 ? " รหัสไปรษณีย์ " + (dr[i]["ICM_CON_POST"]).ToString() : "");
                        infoMasContact.All_address += ((dr[i]["ICM_CON_PROVINCE"]).ToString().Length > 0 ? " จังหวัด" + (dr[i]["ICM_CON_PROVINCE"]).ToString() : "");

                        infoMasContact.InfoGroup = infoGroup;
                        info.InfoMasContact = infoMasContact;
                        info.InfoVat = infoVat;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public void Insert(BidderInfo infoBidder, IList<BidderQuotationInfo> infoListBidderQuotation, IList<DocumentInfo> infoListFile)
        {
            string strSQL;
            DataSet ds;
            int lastSeq = 0;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@icm_con_seq", infoBidder.InfoMasContact.Seq);
                dbManager.AddParameters(1, "@icm_pr_seq", infoBidder.InfoPurchaseRequest.Seq);
                dbManager.AddParameters(2, "@icm_vat_seq", infoBidder.InfoVat.Seq);
                dbManager.AddParameters(3, "@vat_percent", infoBidder.VatPercent);
                dbManager.AddParameters(4, "@icm_bidder_date", infoBidder.Date);
                dbManager.AddParameters(5, "@due_day", infoBidder.DueDay);
                dbManager.AddParameters(6, "@icm_dur_type_seq", infoBidder.InfoDurationType.Seq);
                dbManager.AddParameters(7, "@due_date", infoBidder.DueDate);
                dbManager.AddParameters(8, "@created_by", infoBidder.InfoRecord.Created_by);
                dbManager.AddParameters(9, "@created_when", infoBidder.InfoRecord.Created_when);

                strSQL = "Insert into icm_bidder ( icm_con_seq, icm_pr_seq, icm_vat_seq, vat_percent, icm_bidder_date, due_day, icm_dur_type_seq, due_date" +
                         ",created_by, created_when)" +
                         "values(@icm_con_seq, @icm_pr_seq, @icm_vat_seq, @vat_percent, @icm_bidder_date, @due_day, @icm_dur_type_seq, @due_date" +
                         ",@created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "SELECT LAST_INSERT_ID()"
                   + " FROM icm_bidder";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }

                BidderQuotationDAL dalBidderQuo = new BidderQuotationDAL();
                dalBidderQuo.Insert(lastSeq, infoListBidderQuotation, dbManager);

                DocumentDAL dalDocument = new DocumentDAL();
                dalDocument.InsertFileToDB(infoListFile, "ICM_BIDDER_FILE", "ICM_BIDDER_SEQ", lastSeq, dbManager);

                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertFromBid(BidderInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@icm_bid_seq", info.InfoBid.Seq);
                dbManager.AddParameters(1, "@contact_seq", info.InfoMasContact.Seq);
                dbManager.AddParameters(2, "@receive_date", info.ReceiveDate);
                dbManager.AddParameters(3, "@receive_time", info.ReceiveTime);
                dbManager.AddParameters(4, "@receive_status", SystemInfo._YesStatus);
                dbManager.AddParameters(5, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(6, "@created_when", info.InfoRecord.Created_when);

                strSQL = "INSERT INTO ICM_BIDDER"
                        + "(ICM_BID_SEQ, ICM_CON_SEQ, ICM_RECEIVE_DATE, ICM_RECEIVE_TIME, RECEIVE_STATUS, DUE_DATE"
                        + ", DUE_DAY, ICM_DUR_TYPE_SEQ, ICM_VAT_SEQ, VAT_PERCENT"
                        + ", CREATED_BY, CREATED_WHEN)"
                        + " VALUES(@icm_bid_seq"
                        + ", @contact_seq"
                        + ", @receive_date"
                        + ", @receive_time"
                        + ", @receive_status"
                        + ", (select require_date"
                            + " from icm_purchase_request pr"
                                + " inner join icm_bid b on pr.icm_pr_seq = b.icm_pr_seq"
                            + " where b.icm_bid_seq = @icm_bid_seq"
                        + ")"
                        + ", (select require_duration"
                            + " from icm_purchase_request pr"
                                + " inner join icm_bid b on pr.icm_pr_seq = b.icm_pr_seq"
                            + " where b.icm_bid_seq = @icm_bid_seq"
                        + ")"
                        + ", (select icm_dur_type_seq"
                            + " from icm_purchase_request pr"
                                + " inner join icm_bid b on pr.icm_pr_seq = b.icm_pr_seq"
                            + " where b.icm_bid_seq = @icm_bid_seq"
                        + ")"
                        + ", (select icm_vat_seq"
                            + " from icm_mas_contact"
                            + " where icm_con_seq = @contact_seq"
                        + ")"
                        + ", CASE WHEN (select icm_vat_seq from icm_mas_contact where icm_con_seq = @contact_seq) = 3"
                            + " THEN 0"
                            + " ELSE (select default_vat_percent from icm_sys_control)"
                        + " END"
                        + ", @created_by"
                        + ", @created_when"
                        + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(BidderInfo infoBidder)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@icm_bidder_seq", infoBidder.Seq);
                dbManager.AddParameters(1, "@icm_con_seq", infoBidder.InfoMasContact.Seq);
                dbManager.AddParameters(2, "@icm_vat_seq", infoBidder.InfoVat.Seq);
                dbManager.AddParameters(3, "@vat_percent", infoBidder.VatPercent);
                dbManager.AddParameters(4, "@icm_bidder_date", infoBidder.Date);
                dbManager.AddParameters(5, "@due_day", infoBidder.DueDay);
                dbManager.AddParameters(6, "@icm_dur_type_seq", infoBidder.InfoDurationType.Seq);
                dbManager.AddParameters(7, "@due_date", infoBidder.DueDate);
                dbManager.AddParameters(8, "@receive_date", infoBidder.ReceiveDate);
                dbManager.AddParameters(9, "@offer_date", infoBidder.OfferDate);
                dbManager.AddParameters(10, "@offer_time", infoBidder.OfferTime);
                dbManager.AddParameters(11, "@offer_status", (infoBidder.OfferDate != null ? SystemInfo._YesStatus : SystemInfo._NoStatus));
                dbManager.AddParameters(12, "@updated_by", infoBidder.InfoRecord.Updated_by);
                dbManager.AddParameters(13, "@updated_when", infoBidder.InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_BIDDER"
                    + " SET ICM_CON_SEQ = @icm_con_seq"
                        + " , ICM_VAT_SEQ = @icm_vat_seq"
                        + " , VAT_PERCENT = @vat_percent"
                        + " , ICM_BIDDER_DATE = @icm_bidder_date"
                        + " , DUE_DAY = @due_day"
                        + " , ICM_DUR_TYPE_SEQ = @icm_dur_type_seq"
                        + " , DUE_DATE = @due_date"
                        + " , ICM_RECEIVE_DATE = @receive_date"
                        + " , ICM_OFFER_DATE = @offer_date"
                        + " , ICM_OFFER_TIME = @offer_time"
                        + " , OFFER_STATUS = @offer_status"
                        + " , UPDATED_BY = @updated_by"
                        + " , UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_BIDDER_SEQ = @icm_bidder_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                BidderQuotationDAL dalBidderQuo = new BidderQuotationDAL();

                dalBidderQuo.RecalculateVat((int)infoBidder.Seq, (int)infoBidder.InfoVat.Seq, (double)infoBidder.VatPercent, dbManager);

                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<BidderInfo> GetSellerPurchaseOrder(string search_text)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<BidderInfo> infoList = new List<BidderInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@search_text", search_text);
                dbManager.AddParameters(1, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT main.*"
                        + " FROM ("
                        + "SELECT bd.ICM_BIDDER_SEQ"
                            + ", pm.ICM_PC_METHOD_NAME"
                            + ", cg.ICM_CON_GRP_NAME"
                            + ", CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'), '')) AS ICM_CON_NAME"
                            + ", pr.ICM_PR_REF_NO AS REF_NO"
                            + ", pr.ICM_PR_NAME AS  NAME"
                            + ", SUM(COALESCE(q.NET_AMOUNT,0)) AS NET_AMOUNT"
                            + ", pt.ICM_PC_TYPE_NAME"
                            + ", pr.RECORD_STATUS"
                        + " FROM ICM_BIDDER bd"
                            + " LEFT JOIN ICM_BIDDER_QUOTATION q ON bd.ICM_BIDDER_SEQ = q.ICM_BIDDER_SEQ"
                            + " LEFT JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = bd.ICM_PR_SEQ"
                            + " LEFT JOIN ICM_MAS_CONTACT c ON bd.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " LEFT JOIN ICM_SYS_CONTACT_GROUP cg ON c.ICM_CON_GRP_SEQ = cg.ICM_CON_GRP_SEQ"
                            + " LEFT JOIN ICM_MAS_PURCHASE_METHOD pm ON pr.ICM_PC_METHOD_SEQ = pm.ICM_PC_METHOD_SEQ"
                            + " LEFT JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                        + " WHERE q.SELECTED = @yes_status"
                        + " GROUP BY bd.ICM_BIDDER_SEQ"
                            + ", pm.ICM_PC_METHOD_NAME"
                            + ", cg.ICM_CON_GRP_NAME"
                            + ", CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'), ''))"
                            + ", pr.ICM_PR_REF_NO"
                            + ", pr.ICM_PR_NAME"
                            + ", pt.ICM_PC_TYPE_NAME"
                            + ", pr.RECORD_STATUS"
                        + " UNION ALL "
                        + " SELECT bd.ICM_BIDDER_SEQ"
                            + ", pm.ICM_PC_METHOD_NAME"
                            + ", cg.ICM_CON_GRP_NAME"
                            + ", CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'), '')) AS ICM_CON_NAME"
                            + ", b.ICM_BID_REF_NO AS REF_NO"
                            + ", b.ICM_BID_NAME AS NAME"
                            + ", SUM(COALESCE(q.NET_AMOUNT,0)) AS NET_AMOUNT"
                            + ", pt.ICM_PC_TYPE_NAME"
                            + ", b.RECORD_STATUS"
                        + " FROM ICM_BIDDER bd"
                            + " LEFT JOIN ICM_BID b ON bd.ICM_BID_SEQ = b.ICM_BID_SEQ"
                            + " LEFT JOIN ICM_BIDDER_QUOTATION q ON bd.ICM_BIDDER_SEQ = q.ICM_BIDDER_SEQ"
                            + " LEFT JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = b.ICM_PR_SEQ"
                            + " LEFT JOIN ICM_MAS_CONTACT c ON bd.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " LEFT JOIN ICM_SYS_CONTACT_GROUP cg ON c.ICM_CON_GRP_SEQ = cg.ICM_CON_GRP_SEQ"
                            + " LEFT JOIN ICM_MAS_PURCHASE_METHOD pm ON pr.ICM_PC_METHOD_SEQ = pm.ICM_PC_METHOD_SEQ"
                            + " LEFT JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                        + " WHERE q.SELECTED = @yes_status"
                        + " GROUP BY bd.ICM_BIDDER_SEQ"
                            + ", pm.ICM_PC_METHOD_NAME"
                            + ", cg.ICM_CON_GRP_NAME"
                            + ", CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'), ''))"
                            + ", b.ICM_BID_REF_NO"
                            + ", b.ICM_BID_NAME"
                            + ", pt.ICM_PC_TYPE_NAME"
                            + ", b.RECORD_STATUS"
                        + ") main"
                        + " WHERE RECORD_STATUS = @approve_status"
                            + " AND ICM_BIDDER_SEQ NOT IN (select icm_bidder_seq from icm_purchase_order where record_status <> @delete_status)" 
                            + " AND CASE WHEN @search_text = '' THEN 0 = 0"
                                + " ELSE (ICM_PC_METHOD_NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " or ICM_CON_GRP_NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " or ICM_CON_NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " or ICM_PC_TYPE_NAME LIKE CONCAT('%', @search_text , '%')"
                                + ")"
                            + " END";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BidderInfo info = new BidderInfo();
                        PurchaseRequestInfo infoPr = new PurchaseRequestInfo();
                        MasterPurchaseMethodInfo infoPcMethod = new MasterPurchaseMethodInfo();
                        MasterContactInfo infoMasContact = new MasterContactInfo();
                        SysContactGroupInfo infoContactGroup = new SysContactGroupInfo();
                        MasterPurchaseTypeInfo infoPcType = new MasterPurchaseTypeInfo();
                        BidInfo infoBid = new BidInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_BIDDER_SEQ"]);
                        infoPcMethod.Name = dr[i]["ICM_PC_METHOD_NAME"].ToString();
                        infoPcType.Name = dr[i]["ICM_PC_TYPE_NAME"].ToString();
                        infoMasContact.Name_th = dr[i]["ICM_CON_NAME"].ToString();
                        infoBid.Ref_no = dr[i]["REF_NO"].ToString();
                        infoBid.Name = dr[i]["NAME"].ToString();
                        info.NetAmount = Convert.ToDouble(dr[i]["NET_AMOUNT"]);
                        infoContactGroup.Name = dr[i]["ICM_CON_GRP_NAME"].ToString();

                        infoPr.InfoPurchaseType = infoPcType;
                        infoPr.InfoPurchaseMethod = infoPcMethod;
                        infoMasContact.InfoGroup = infoContactGroup;
                        info.InfoMasContact = infoMasContact;
                        info.InfoPurchaseRequest = infoPr;
                        info.InfoBid = infoBid;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public void DeleteFromDB(IList<BidderInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();

                strSQL = "DELETE FROM ICM_BIDDER"
                    + " WHERE ICM_BIDDER_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateWinnerStatus(int bidder_seq, IDBManager dbManagerTran)
        {
            string strSQL;

            try
            {
                dbManagerTran.CreateParameters(3);
                dbManagerTran.AddParameters(0, "@yes_status", SystemInfo._YesStatus);
                dbManagerTran.AddParameters(1, "@no_status", SystemInfo._NoStatus);
                dbManagerTran.AddParameters(2, "@bidder_seq", bidder_seq);

                strSQL = "UPDATE ICM_BIDDER"
                    + " SET WINNER_STATUS = "
                        + " CASE WHEN coalesce((select count(*) from icm_bidder_quotation where icm_bidder_seq = @bidder_seq),0) > 0"
                            + " THEN @yes_status"
                            + " ELSE @no_status"
                        + " END"
                    + " WHERE ICM_BIDDER_SEQ = @bidder_seq";

                dbManagerTran.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
        }
    }
}
